Shareware Grab Bag/Shareware Grab Bag.iso/003/db3tips3.arc/DB3MISC.TXT

Text File | 1986-12-21 | 18KB | 478 lines

                 Preformatted dBASE SUM Command
       (PC Magazine Vol 5 No 15 Sept 16, 1986 Power User)

     The dBASE SUM command reports totals of numeric fields, but its
output is Spartan at best -- just the raw numbers and their field
names.  If you need a presentable record of the state of your data
file on a given date, it just won't do.
     Here's a useful idea for transforming dBASE's Report Form
capability into a a much-enhanced SUM command.  Like SUM, it rapidly
totals a file; unlike SUM, however, the totals are clearly,
consistently formatted with headings and date.  You don't even have
to retype the long SUM command next time.
     You start by making a copy of any .FRM file you've written
previously.  Next, issue the MODIFY REPORT command and, at the second
screen, specify " " as the Subtotal Field.  Then answer Y to the
inquiry about wanting a Summary Report.  Finally, go back and remove
the Subtotal Field, then save the .FRM file to disk and run it.  When
you run a report modified in this way, only the headings and totals
print out, saving a lot of time and paper.
     Editor's Note:  This tip cons dBASE into buying Screen 2 of the
Report Form.  It hustles dBASE into accepting nothing (" ") as a
Subtotal Field.  (You have to make some entry there or dBASE won't
give you the chance to specify Summary Report.)  If you just save the
report with " " as the Subtotal Field, however, dBASE will judge that
every record fits within the first subtotal group.  You'll get the
right totals, but they'll be reported twice -- first as subtotals,
then as totals.
     However, by then modifying the Report Form by removing the " ",
you further trick dBASE into suppressing the "**SUBTOTAL" line
entirely.  After that, whenever you want global totals from your file,
just type REPO FORM filename.
     You could use this same .FRM to total segments of a file by
adding a SET FILTER TO or a FOR condition.  Or you could make a
permanent disk record of this nicely formatted data by adding TO FILE
filename to the REPORT command.

-----------------------------------------------------------------
                       1-2-3 to dBASE III
       (PC Magazine Vol 5 No 15 Sept 16, 1986 Power User)

     An alternative procedure for converting 123 worksheets to dBASE
III is to create a 123 print file whose range includes only the data
(not the headings).  Set the left margin to 0 and the right to 240.
Select Options Other Unformatted, then Go.  For best results, reset
any numeric formatting options in the spreadsheet first.
     Your dBASE file structure must be set up with the same field
widths as the 123 columns.  The dBASE import command is:

APPEND FROM filename.PRN SDF

     Editor's Note:  The file produced by 123 is in pure ASCII format
with each spreadsheet row delimited by a carriage return-line feed.
Most word processors and many other applications can read this format
as well.  If you find it useful to export data often, set up a 123
macro.  Create and name the data cell range.  Then execute:

\RFR rangename~
\RR rangename~
\PF filename.PRN~
\OOUML0~MR240~MT0~MB0~QAGQ

Exporting from dBASE back to 123 is as easy as typing:

COPY TO filename.TXT DELIMITED

DELIMITED causes dBASE to format the output file with each field
separated by a comma and character fields enclosed in double quotes.
Once back in 123, position the cursor where you want the data to drop
in and type:

\FIN filename.TXT

123 will note the commas and create the column structure accordingly,
converting the quoted strings into labels.  Unfortunately, something
is lost in the translation -- your formulas.  What you see is what you
get.  Another potential problem is 123's unfortunate 240-character
width limit for importing and exporting.  If these limits don't get in
the way, however, you can switch a file back and forth between 123 and
dBASE, taking advantage of each program's special strengths.
     One of the advantages of the new dBASE III Plus is that it will
allow you to APPEND 1-2-3 .WKS files directly.

-----------------------------------------------------------------
               Printing a Field on Multiple Lines

     The following dBASE III routine can be used to print a field on
multiple lines.  Each field that is passed to the program will be split
into substrings of the specified length or shorter.  Words will not be
split.  The substrings will be printed starting at the row/column
coordinates.  Syntzx to call the program is:

DO WRAP WITH fieldname, length, row, col

* Program:  WRAP.PRG
parameters string, length, row, col

strlen = LEN(string)
DO WHILE strlen > length
  spot = length
  * Do not divide a word
  DO WHILE SUBSTR (string, spot, 1) # " "
    spot = spot - 1
  ENDDO
  @ row, col SAY SUBSTR(string, 1, spot - 1)
  row = row + 1
  string = SUBSTR(string, spot+1, strlen-spot)
  strlen = LEN(string)
ENDDO
@ row, col SAY string
RETURN

-----------------------------------------------------------------
                          Safety First
       (PC Magazine Vol 5 No 16 Sept 30, 1986 Power User)

     Before upgrading to dBASE III Plus, you should first install the
program in a separate subdirectory so you can test whether your
existing Version 1.1 programs will still run.  If you want to run Plus
with your old programs, one immediaet change you'll want to make is to
remove the STATUS=ON and HELP=ON lines from Plus's standard CONFIG.DB
file.  By installing Plus in this tentative way, you can switch back
to the older, tested version simply by loading it from its own
directory.  Later, when you're absolutely convinced, you can deinstall
both versions and put Plus on the normal directory from which you've
loaded dBASE in the past.
     Editor's Note:  There is far less difference between dBASE III
Plus and dBASE III than between III and II.  In fact, there is very
little in the new version that will cause problems with your older
programs, but there are some gremlins.
     For example, SET COLOR TO commands now use letters instead of
numbers.  Any applications that use screen enhancements such as
underline or reverse video for clarity must be revised; and, once
revised, they won't run properly in the older dBASE.
     Plus takes more than twice as much disk space -- up from 149K to
312K.  The old standard workstation -- a dual floppy system with dBASE
and applications programs on drive A: and data on drive B: -- is
strictly a thing of the past.  dBASE III Plus takes more operating
memory, too.  If you have only 256K, you're up a creek; while Ashton-
Tate supplies a paddle in the form of a bare-bones CONFIG.DB, memory
is strictly rationed and there's little available for such memory-
intensive functions as indexing and sorting.  If you couldn't work
a day without resident programs like ProKey or SideKick, you're out
of work until you add more RAM.
     There are lots of little things to complain about.  The report
and label generators are simply terrible; while adding no new
capabilities, they give you much less information and require many
more steps to complete the same work.  In the interactive mode, the
cursor jumps to line 24 each time the dot prompt appears.  This makes
it difficult or impossible to leave information on the screen while
you ask questions, make replacements, etc.

-----------------------------------------------------------------
                            Eyes Left
        (PC Magazine Vol 5 No 20 Nov 25, 1986 Power User)

     dBASE's TRIM() function is perfect for removing trailing blanks,
but neither dBASE II nor Version 1 of dBASE III offers the reverse:
an easy way to trim leading blanks.  LTRIM.PRG performs this function,
assuming you're working with a character field.

LTRIM.PRG:

CLEAR
SET TALK OFF
ACCE "LTRIM which FILE?" TO fil
ACCE "REPL which FIELD?" TO fld
USE &fil
DO WHIL .NOT. EOF()
  ref=1
  DO WHIL ref<LEN(&fld)
    ref2=SUBS(&fld,ref,1)
    IF ref2#' '
      EXIT
    ELSE
      ref=ref+1
    ENDIF
  ENDDO
  REPL &fld with SUBS(&fld,ref)
  ? &fld
  SKIP
ENDDO
SET TALK ON

     Editor's Note:  dBASE III Plus solves this sometimes annoying
problem by adding a direct function, LTRIM():REPL <filename) WITH
LTRIM(<filename>).  This new LTRIM() applies only to character-type
fields.
     If you have an older version of dBASE, however, LTRIM.PRG will
come in handy if, for example, you want to clean up a name field that
has leading blanks, or if you need to index a file on the ZIP code
and you know that some ZIPs have not been entered flush-left.
     The general technique -- indexing right from the leftmost
character -- can be useful in different ways.  Suppose you want to
express a number flush-left, and it's inconvenient or impossible to
use dBASE III's PICTURE "@B" function.  You could modify the structure
of the numeric field, converting it to character format.  At this
point, all the numeric data in your new character field is flush-
right.  The LTRIM.PRG will eat the leading blanks, pulling the
character string over to the leftmost position.
     With a few changes you can even LTRIM on the fly, without
modifying the data structure from numeric to character and without
replacing the contents.  If you fieldname for a numeric field 8 digits
wide is called "Numbr", the following routine will create a left-
ustrified (and TRIMed) character string, no matter what value is in
the Numbr field:

fld=STR(Numbr,8)
ref=1
DO WHIL fld=' ' .AND. ref<8
  fld=SUBS(fld,2)
  ref=ref+1
ENDDO
fld=TRIM(fld)

If you're LTRIMing a character field on the fly, only the first and
third lines differ:

fld=chrfield
ref=1
DO WHIL fld=' ' .AND. ref<LEN(chrfield)
  fld=SUBS(fld,2)
  ref=ref+1
ENDDO
fld=TRIM(fld)

If you're using dBASE II, remember that the syntax to STORe memory
variables is different.  The last line above would read:

STOR TRIM(fld) TO fld

-----------------------------------------------------------------
                        High-Power Pause
        (PC Magazine Vol 5 No 20 Nov 25, 1986 Power User)

     The procedure below gives you an easy way to display messages
from with a dBASE application.  For example, if a menu offers choices
of 1-9 and the user enters "k", the program would trap this impossible
response as follows:

DO WHILE .T.
  * insert you menu screen here
  * GET user's choice and READ
  IF .NOT. response $ "123456789"
    DO pause WITH [No choice "]+response+["]
  ELSE
    EXIT
  ENDIF error-trap
ENDDO menu

     If the user's response is not allowable, the DO WHILE loop stays
active, reprinting the menu screen and reGETting the user's response.
Thus, if the user gets past your error trap, you know it's safe to
process his response.
     Editor's Note:  Although the PAUSE.PRG below was designed for
noncritical error messages, if you pause long enough to think it
through, you'll probably find a lot of other uses for this handy
dBASE III procedure.  As examples, you might DO pause WITH any of
the following:

     "Don't forget to backup this new data."
     "Remember to invoice this extra item."
     "Bob wants THREE copies of this report."
     "REM each record MUST have a zip before indexing."
     "Please verify coding on West Coast items."
     "Verify: this check amount is over $1,000."

     PAUSE.PRG thus has a lot of horsepower under the hood for such a
short procedure -- some of these might be useful in other programs you
write.
     For example, by passing the message as a parameter, you can send
the user a context-sensitive message.  It surrounds your message with
arrows and a space.  It centers the message: the expression, (80-LEN
(mess))/2, asks the computer to pace off half the distance from column
39 depending on the LENgth of the current message.  (Note: maximum
message length is 76 characters.)  This same line also enhances the
message: unless you have SET INTENSITY OFF, the GET appears in reverse
video.  Of course, you're not actually GETting anything, so the CLEAR
GETS straightens that out.
     PAUSE displays on the same screen line every time, so the user
becomes accustomed to receiving minor error messages there.  (You could
choose a different line just by changing the @24s.)  It beeps to alert
the user that an unusual condition exists.
     Finally, having delivered its message, PAUSE gets out of the way
without requiring a keystroke.  It tidies up by erasing its message
and RETUrns to the calling program.  You might even ask the user at
the beginning of a session "How long do you want reminders displayed?"
and STORE that number to a variable called "duration."  Then change the
DO WHILEx<25 loop to DO WHILE x<duration.

* PAUSE.PRG
PARA mess
? CHR(7)
SET CONS OFF
mess=CHR(26)+" "+mess+" "+CHR(27)
@ 24,0
@ 24,(80-LEN(mess))/2 GET mess
CLEA GETS
x=0
DO WHIL x<25
x=x+1
ENDD x=message duration
@ 24,0
SET CONS ON

-----------------------------------------------------------------
                      dBASE Subdirectories
             (PC World Star-Dot-Star November 1986)

     Subdirectories make organizing your hard disk a breeze because
you can put related data and programs into logical places.  dBASE III
Plus lacks a simple command to change the current directory.
     It's possible to use the command RUN CD path to accommodate this
end, but that requires additional memory, plus extra time to load the
DOS command interpreter.  Fortunately, dBASE III Plus can easily load
and execute assembly language programs.  CD.PRG and CD.BIN use this
feature to change the current directory.  CD.PRG closes any open data
files, loads the assembly language program CD.BIN into memory,
transfers control to CD.BIN, then eliminates CD.BIN from memory and
returns control to the calling program.
     Use DEBUG to create CD.BIN as shown below.  Store both CD.PRG and
CD.BIN in the same subdirectory that contains dBASE III Plus.  To use
the programs, simply issue the command DO CD WITH path, replacing path
with the appropriate subdirectory specification.

* CD.PRG
parameter dir
close databases
load cd
call cd with dir
? 'Directory now changed to ',dir
release cd
return

CD.BIN:
A>DEBUG
-A 100
XXXX:0100 MOV DX,BX
XXXX:0102 MOV AX,3B00
XXXX:0105 INT 21
XXXX:0107 RETF
XXXX:0108
-N CD.BIN
CX 0000
:8
-W
-Q

-----------------------------------------------------------------
                     dBASE Cross Tabulation
        (PC Magazine Vol 5 No 22 Dec 23, 1986 Power User)

     Occasionally, you need COUNTs for all possible combinations of
certain fields in dBASE files.  Multiway tabulation (e.g., "How are
people in a database distributed by sex and age?") is a fairly common
tool in statistics, and it is sometimes seen in other contexts.  To do
this with a series of COUNTs or TOTALs, however, requires that the
whole sequence of instructions be assembled from scratch for each
specific query.
     XTAB.PRG offers a more general solution, though it can process
only one table at a time.  To use the program, issue the command:

DO XTAB

The procedure GETs the data filename, the number of variables (i.e.,
fields or expressions) to be tabulated, and the name of each one.
Then it creates a summary database to store the resulting table (ending
in .DBX) and begins processing.
     Editor's Note:  Life would be simpler if dBASE would allow you to
TOTAL ON more than one field.  Then, to your original file you could
just add a numeric field caller counter (a length of 4 would usually
be sufficient), REPL ALL counter WITH 1,INDEX ON key expression, and
TOTAL ON key expression.

** XTAB.PRG
SET TALK OFF
CLEAR
nvar=0
origfile=SPAC(20)
@  3,0 SAY "CROSS TAB PROGRAM"
@  5,0 SAY "Which file? " GET origfile
READ
origfile=TRIM(origfile)
@  7,0 SAY "How many variables?" GET nvar
READ
SELE 1
USE &origfile ALIA origfile
keyexp=""
* keyexp = list of vars delimited with plus signs
i=1
DO WHILE i<=nvar .AND. LEN(keyexp)<250
  nomvar=SPAC(25)
  @ 8+i,0 SAY "Variable "+STR(i,2)+" field/expression? " GET nomvar
  READ
  nomvar=TRIM(nomvar)
  IF TYPE([&nomvar})='N'
    * Note: change below if decimals
    keyexp=keyexp+'STR('+nomvar+',10,0)'
  ELSE
    keyexp=keyexp+nomvar
  ENDIF
  IF i<nvar
    keyexp=keyexp+"+[ ]+"
  ENDIF
  i=i+1
ENDDO
* create summary file
SET SAFE OFF
COPY STRU EXTENDED TO tempsum
SELE 2
USE tempsum
DELE FOR RECNO()>2
PACK
GOTO 1
REPL field_name WITH "TOT",field_type WITH "N",;
     field_len WITH 5,field_dec WITH 0
sumname=TRIM(origfile)+".dbx"
CREATE &sumname FROM tempsum
USE &sumname
ERAS tempsum.dbf
* scanning the data file
? "Standby ....processing"
SELE 1
INDEX ON &keyexp TO temp
SET INDEX TO temp
SET SAFE ON
keyref=&keyexp
DO WHILE .NOT. EOF()
  COUN WHILE &keyexp=keyref TO ncount
  SELE 2
  APPEND BLANK
  REPL tot WITH ncount,key WITH keyref
  SKIP
  SELE 1
  keyref=&keyexp
ENDDO
CLEAR
SELE 2
DISP OFF ALL tot,key
CLOSE DATA
* Optional: ERAS temp.ndx
SET TALK ON
RETU

-----------------------------------------------------------------
                         Flexible Signal
        (PC Magazine Vol 5 No 22 Dec 23, 1986 Power User)

     When running a long dBASE process, you want to know when it's
through so you can do something else in the meantime.  The short
SIGNAL.PRG is loud enough to be heard from the next room.  Some users
might like to add a WAIT statement at the end.

* SIGNAL.PRG
? CHR(7)+TIME()
?? CHR(7)+" - Process Complete."
?? CHR(7)
?? CHR(7)
?? CHR(7)
?? CHR(7)
?? CHR(7)
?? CHR(7)

     Editor's Note:  If signaling is useful in your dBASE programs,
you may want to develop this theme.  For example, by combining The
Norton Utilities with dBASE's RUN capability, you could signal a major
error with an SOS in Morse code thus:

    RUN BEEP /f4000 /r3 /d1
    RUN BEEP /f4000 /r3 /d3
    RUN BEEP /f4000 /r3 /d1

A musically minded user may want to announce that he's ready to start
his process with:

    RUN BEEP /f1000 /r3 /d1
    RUN BEEP /f800 /d8